const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔍 分析所有实例表结构和属性字段完整性');
console.log('===============================================');

try {
  // 1. 搜索所有device_instances开头的表
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 找到 ${instanceTables.length} 个实例表:`);
  instanceTables.forEach((table, index) => {
    console.log(`  ${index + 1}. ${table.name}`);
  });
  
  // 2. 获取所有设备类型映射
  const deviceMappings = db.prepare(`
    SELECT classification_name, specialty, naming_table, position_table, base_params_table
    FROM device_type_mappings
    ORDER BY specialty, classification_name
  `).all();
  
  console.log(`\n📋 设备类型映射 (${deviceMappings.length}个):`);
  deviceMappings.forEach(mapping => {
    console.log(`  ${mapping.specialty}: ${mapping.classification_name}`);
    console.log(`    命名: ${mapping.naming_table}, 位置: ${mapping.position_table}, 参数: ${mapping.base_params_table}`);
  });
  
  // 3. 分析每个实例表
  const analysisResults = [];
  
  for (const table of instanceTables) {
    console.log(`\n🔍 分析实例表: ${table.name}`);
    console.log('='.repeat(60));
    
    try {
      // 获取表结构
      const tableInfo = db.prepare(`PRAGMA table_info(${table.name})`).all();
      const systemFields = ['id', 'created_at', 'updated_at'];
      const businessFields = ['device_type_code', 'instance_number', 'object_number', 'object_description'];
      
      const dataFields = tableInfo.filter(col => !systemFields.includes(col.name));
      const actualBusinessFields = dataFields.filter(col => businessFields.includes(col.name));
      const attributeFields = dataFields.filter(col => !businessFields.includes(col.name));
      
      console.log(`  总字段数: ${tableInfo.length}`);
      console.log(`  系统字段: ${systemFields.length}个`);
      console.log(`  业务字段: ${actualBusinessFields.length}个 (期望4个)`);
      console.log(`  属性字段: ${attributeFields.length}个`);
      
      // 按前缀分类属性字段
      const fieldsByPrefix = {
        'naming_': [],
        'position_': [],
        'base_params_': [],
        'other': []
      };
      
      attributeFields.forEach(field => {
        if (field.name.startsWith('naming_')) {
          fieldsByPrefix['naming_'].push(field.name.replace('naming_', ''));
        } else if (field.name.startsWith('position_')) {
          fieldsByPrefix['position_'].push(field.name.replace('position_', ''));
        } else if (field.name.startsWith('base_params_')) {
          fieldsByPrefix['base_params_'].push(field.name.replace('base_params_', ''));
        } else {
          fieldsByPrefix['other'].push(field.name);
        }
      });
      
      console.log(`  前缀字段分布:`);
      console.log(`    naming_: ${fieldsByPrefix['naming_'].length}个`);
      console.log(`    position_: ${fieldsByPrefix['position_'].length}个`);
      console.log(`    base_params_: ${fieldsByPrefix['base_params_'].length}个`);
      console.log(`    其他: ${fieldsByPrefix['other'].length}个`);
      
      // 尝试从表名推断设备类型
      let deviceMapping = null;
      
      // 从表名提取专业和设备类型信息
      const tableNameParts = table.name.replace('device_instances_', '').split('_');
      const specialty = tableNameParts[0]; // piping, electrical, etc.
      
      // 尝试匹配设备类型映射
      for (const mapping of deviceMappings) {
        if (mapping.specialty === specialty) {
          // 更复杂的匹配逻辑可以在这里实现
          // 现在先记录第一个匹配的专业
          if (!deviceMapping) {
            deviceMapping = mapping;
          }
        }
      }
      
      // 分析结果记录
      const analysis = {
        tableName: table.name,
        totalFields: tableInfo.length,
        systemFields: systemFields.length,
        businessFields: actualBusinessFields.length,
        attributeFields: attributeFields.length,
        prefixFields: fieldsByPrefix,
        deviceMapping: deviceMapping,
        issues: []
      };
      
      // 检查业务字段完整性
      const missingBusinessFields = businessFields.filter(field => 
        !actualBusinessFields.some(f => f.name === field)
      );
      if (missingBusinessFields.length > 0) {
        analysis.issues.push(`缺少业务字段: ${missingBusinessFields.join(', ')}`);
      }
      
      // 检查是否使用了前缀命名
      const hasPrefix = fieldsByPrefix['naming_'].length > 0 || 
                       fieldsByPrefix['position_'].length > 0 || 
                       fieldsByPrefix['base_params_'].length > 0;
      
      if (!hasPrefix) {
        analysis.issues.push('未使用前缀命名规则');
      }
      
      // 检查其他字段（可能是旧格式）
      if (fieldsByPrefix['other'].length > 0) {
        analysis.issues.push(`发现非前缀字段: ${fieldsByPrefix['other'].join(', ')}`);
      }
      
      analysisResults.push(analysis);
      
      if (analysis.issues.length > 0) {
        console.log(`  ⚠️  发现问题:`);
        analysis.issues.forEach(issue => console.log(`    - ${issue}`));
      } else {
        console.log(`  ✅ 结构正常`);
      }
      
    } catch (error) {
      console.log(`  ❌ 分析失败: ${error.message}`);
      analysisResults.push({
        tableName: table.name,
        error: error.message
      });
    }
  }
  
  // 4. 生成总结报告
  console.log('\n📊 实例表结构分析总结');
  console.log('===============================================');
  
  const goodTables = analysisResults.filter(r => !r.error && r.issues.length === 0);
  const problematicTables = analysisResults.filter(r => !r.error && r.issues.length > 0);
  const errorTables = analysisResults.filter(r => r.error);
  
  console.log(`✅ 结构正常的表: ${goodTables.length}个`);
  goodTables.forEach(table => {
    console.log(`  ${table.tableName} - ${table.attributeFields}个属性字段`);
  });
  
  console.log(`\n⚠️  需要更新的表: ${problematicTables.length}个`);
  problematicTables.forEach(table => {
    console.log(`  ${table.tableName}:`);
    table.issues.forEach(issue => console.log(`    - ${issue}`));
  });
  
  if (errorTables.length > 0) {
    console.log(`\n❌ 错误的表: ${errorTables.length}个`);
    errorTables.forEach(table => {
      console.log(`  ${table.tableName}: ${table.error}`);
    });
  }
  
  // 5. 提供详细的更新建议
  if (problematicTables.length > 0) {
    console.log('\n🔧 更新建议');
    console.log('===============================================');
    
    problematicTables.forEach(table => {
      console.log(`\n📋 ${table.tableName}:`);
      
      // 分析需要的更新
      const updates = [];
      
      if (table.businessFields < 4) {
        updates.push('添加缺少的业务字段');
      }
      
      if (table.prefixFields['naming_'].length === 0) {
        updates.push('添加naming_前缀的命名表字段');
      }
      
      if (table.prefixFields['position_'].length === 0) {
        updates.push('添加position_前缀的位置表字段');
      }
      
      if (table.prefixFields['base_params_'].length === 0) {
        updates.push('添加base_params_前缀的基础参数表字段');
      }
      
      if (table.prefixFields['other'].length > 0) {
        updates.push('重命名或移除非前缀字段');
      }
      
      updates.forEach(update => console.log(`  - ${update}`));
    });
  }
  
  // 6. 生成确认问题
  console.log('\n❓ 确认问题');
  console.log('===============================================');
  console.log(`是否需要更新 ${problematicTables.length} 个实例表以符合前缀命名规则？`);
  console.log('更新内容包括:');
  console.log('1. 添加缺少的业务字段 (device_type_code, instance_number, object_number, object_description)');
  console.log('2. 添加命名表字段 (naming_前缀)');
  console.log('3. 添加位置表字段 (position_前缀)');
  console.log('4. 添加基础参数表字段 (base_params_前缀)');
  console.log('5. 处理现有非前缀字段');
  
  console.log('\n⚠️  注意: 更新操作会修改表结构，建议先备份数据');
  
} catch (error) {
  console.error('❌ 分析失败:', error.message);
} finally {
  db.close();
}